/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Bean.DepartmentBean;
import Utility.DBProcess;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Vietlt
 */
public class DepartmentDAO {
    
    public static void main(String[] args) {
        DepartmentBean ab = new DepartmentBean();
        ab.setDepartmentID(1);
        ab.setName("name");
        ab.setEmail("ngochang@yahoo");
        ab.setPhoneNumber("1234567");
        ab.setLeaderName("lead");
        ab.setDescription("ko co gi");
        int rs = new DepartmentDAO().UpdateNewDepartment(ab);
        if (rs >0) {
            System.out.println("up Okkkkkkkkkkkkkkkkkkkkkkkkkkk");
        }
        
    }

    public List<DepartmentBean> LoadAllDepartment() {
        List<DepartmentBean> list = new ArrayList<DepartmentBean>();
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        try {
            stm = conn.prepareCall("{ call Department_getall}"); //Applicant_add = Ten Procedure
            rs = stm.executeQuery();
            while (rs.next()) {
                DepartmentBean aB = new DepartmentBean();
                aB.setDepartmentID(rs.getInt("DepartmentID"));
                aB.setName(rs.getString("Name"));
                aB.setEmail(rs.getString("Email"));
                aB.setPhoneNumber(rs.getString("PhoneNumber"));
                aB.setLeaderName(rs.getString("LeaderName"));
                aB.setDescription(rs.getString("Description"));
                list.add(aB);
                if (list.isEmpty()) {
                    String err = "List empty !!!";
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DepartmentDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return list;
    }

    public DepartmentBean LoadAllDepartmentById(int DepartmentID) {
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        DepartmentBean dB = new DepartmentBean();
        try {
            stm = conn.prepareCall("{ call Department_get (?)}"); //Applicant_add = Ten Procedure
            stm.setInt(1, DepartmentID);
            rs = stm.executeQuery();
            while (rs.next()) {
                dB.setDepartmentID(rs.getInt("DepartmentID"));
                dB.setName(rs.getString("Name"));
                dB.setEmail(rs.getString("Email"));
                dB.setPhoneNumber(rs.getString("PhoneNumber"));
                dB.setLeaderName(rs.getString("LeaderName"));
                dB.setDescription(rs.getString("Description"));

            }
        } catch (SQLException ex) {
            Logger.getLogger(DepartmentDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return dB;
    }

    public int AddNewDepartment(DepartmentBean dpB) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        int isComplete = 0;
        try {
            stm = conn.prepareCall("{ call Department_add (?,?,?,?,?)}");
            stm.setString(1, dpB.getName());
            stm.setString(2, dpB.getEmail());
            stm.setString(3, dpB.getPhoneNumber());
            stm.setString(4, dpB.getLeaderName());
            stm.setString(5, dpB.getDescription());
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DepartmentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return isComplete;
    }

    public int UpdateNewDepartment(DepartmentBean upB) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        //ApplicantBean upB=new ApplicantBean();
        int isComplete = 0;
        try {
            stm = conn.prepareCall("{ call Department_update (?,?,?,?,?,?)}");
            stm.setInt(1, upB.getDepartmentID());
            stm.setString(2, upB.getName());
            stm.setString(3, upB.getEmail());
            stm.setString(4, upB.getPhoneNumber());
            stm.setString(5, upB.getLeaderName());
            stm.setString(6, upB.getDescription());
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DepartmentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return isComplete;
    }

    public int DeleteDepartment(int departmentID) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        //ApplicantBean upB=new ApplicantBean();
        int isComplete = 0;
        try {
            stm = conn.prepareCall("{ call Department_delete (?)}");
            stm.setInt(1, departmentID);
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DepartmentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return isComplete;
    }
    
}
